﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CoreMembers.FileHelp
{
    public class NPOIHelpE
    {
        /// <summary>
        /// 数据转dataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="items"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {

                try
                {//将没有[Name("")]标记的排除,切记这些字段一定要放在最后
                    Type t = GetCoreType(prop.PropertyType);
                    tb.Columns.Add(prop.GetCustomAttributesData().FirstOrDefault().ConstructorArguments.FirstOrDefault().Value.ToString(), t);
                }
                catch (Exception)
                {
                    continue;
                }

            }

            foreach (T item in items)
            {
                var values = new object[tb.Columns.Count];

                for (int i = 0; i < tb.Columns.Count; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }

            return tb;
        }
        /// <summary>
        /// 替换列明为数据集合第一行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="items"></param>
        /// <returns></returns>
        public static DataTable ToDataTableNoColumn<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {

                try
                {//将没有[Name("")]标记的排除,切记这些字段一定要放在最后
                    Type t = GetCoreType(prop.PropertyType);
                    tb.Columns.Add(prop.GetCustomAttributesData().FirstOrDefault().ConstructorArguments.FirstOrDefault().Value.ToString(), t);
                }
                catch (Exception)
                {
                    continue;
                }

            }

            int first = 1;
            foreach (T item in items)
            {
                var values = new object[tb.Columns.Count];

                for (int i = 0; i < tb.Columns.Count; i++)
                {
                    if (first == 1)
                    {
                        tb.Columns[i].ColumnName = props[i].GetValue(item, null).ToString();
                    }
                    else
                    {
                        values[i] = props[i].GetValue(item, null);
                    }
                }
                if (first > 1)
                {
                    tb.Rows.Add(values);
                }
                first += 1;
            }

            return tb;
        }
        public static Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }
        public static bool IsNullable(Type t)
        {
            return !t.IsValueType || t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>);
        }
        #region NPOI的dt 转excel 方法
        ///调用方法，将数据导入到excel
        /// <summary>
        /// 根据DataTabled导出Excel
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="headtitle"></param>
        /// <param name="dt">需要生成Excel的数据，形式为</param>
        /// <returns></returns>
        public static string ExportToExcel(DataTable dt, string sheetName, string headtitle)
        {
            IWorkbook workbook = new XSSFWorkbook();
            string fileName = GetGuid() + ".xls";
            var basePath = Path.GetDirectoryName(AppContext.BaseDirectory);
            string path = Path.Combine(basePath, fileName);
            workbook = DataTableToExcel(dt, workbook, sheetName, headtitle);
            using (FileStream fs = File.Create(path))
            {
                workbook.Write(fs);
            }
            return fileName;
        }
        //编写代码，将datatbale转化为NPOI的workbook
        /// <summary>
        /// 将datatbale的数据转化为workbook，并设置相关参数
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="workbook"></param>
        /// <param name="sheetName"></param>
        /// <param name="headtitle">标题（为空的时候默认不加标题）</param>
        /// <returns></returns>
        public static IWorkbook DataTableToExcel(DataTable dt, IWorkbook workbook, string sheetName, string headtitle)
        {
            ISheet sheet = workbook.CreateSheet(sheetName);
            var rowint = 0;
            if (!string.IsNullOrEmpty(headtitle))
            {
                rowint = 1;
                //添加标题头
                var a = dt.Columns.Count;
                if (a > 0)
                {
                    a = a - 1;
                }
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, a));
                IRow rowTitle = sheet.CreateRow(0);
                rowTitle.Height = 30 * 20;//设置行高
                var cellTitel = rowTitle.CreateCell(0);

                //给标题头添加格式
                ICellStyle styleTitle = workbook.CreateCellStyle();
                styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
                IFont font0 = workbook.CreateFont();
                font0.IsBold = true;//设置字体加粗样式
                font0.FontHeightInPoints = 16;//设置字体大小
                styleTitle.SetFont(font0);//使用SetFont方法将字体样式添加到单元格样式中
                cellTitel.CellStyle = styleTitle;
                cellTitel.SetCellValue(headtitle);
            }
            IRow headerRow = sheet.CreateRow(rowint);
            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.BorderBottom = BorderStyle.Thin;
            cellstyle.BorderLeft = BorderStyle.Thin;
            cellstyle.BorderRight = BorderStyle.Thin;
            cellstyle.BorderTop = BorderStyle.Thin;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "Arial";
            //font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            cellstyle.SetFont(font);

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                string columnName = dt.Columns[j].ColumnName;
                ICell cellColumn = headerRow.CreateCell(j);
                cellColumn.SetCellValue(columnName);
                cellColumn.CellStyle = cellstyle;
                int length = Encoding.UTF8.GetBytes(columnName).Length;
                sheet.SetColumnWidth(j, (length + 1) * 256);
            }

            ICellStyle cellstyle1 = workbook.CreateCellStyle();
            cellstyle1.BorderBottom = BorderStyle.Thin;
            cellstyle1.BorderLeft = BorderStyle.Thin;
            cellstyle1.BorderRight = BorderStyle.Thin;
            cellstyle1.BorderTop = BorderStyle.Thin;
            cellstyle1.VerticalAlignment = VerticalAlignment.Center;
            cellstyle1.Alignment = HorizontalAlignment.Center;
            IFont font1 = workbook.CreateFont();
            font1.FontHeightInPoints = 10;
            font1.FontName = "Arial";
            //font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            cellstyle1.SetFont(font1);
            rowint++;
            for (int a = 0; a < dt.Rows.Count; a++)
            {
                DataRow dr = dt.Rows[a];
                IRow row = sheet.CreateRow(a + rowint);
                for (int b = 0; b < dt.Columns.Count; b++)
                {
                    ICell cellRow = row.CreateCell(b);

                    string str = dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty;

                    //因导出格式问题，不再转换 updated by dhp at 2018.12.20
                    //double db = 0;
                    //if (!str.Contains(",") && double.TryParse(str, out db)) cellRow.SetCellValue(db);
                    //else cellRow.SetCellValue(str);
                    cellRow.SetCellValue(str);
                    cellRow.CellStyle = cellstyle1;
                    int columnWidth = sheet.GetColumnWidth(b) / 256;

                    int length = Encoding.UTF8.GetBytes(str).Length;//获取当前单元格的内容宽度  
                    if (columnWidth < length + 1)
                    {
                        columnWidth = length + 1;
                    }

                    if (columnWidth > 255) columnWidth = 255;

                    sheet.SetColumnWidth(b, columnWidth * 256);
                }
            }
            sheet.CreateFreezePane(0, 2, 0, 2);

            return workbook;
        }
        public static string GetGuid()
        {
            string part1 = (DateTime.Now.Ticks / 100000 - 6213559680000).ToString();
            string part2 = Guid.NewGuid().ToString().Substring(0, 4);
            return part1 + part2;
        }
        #endregion
        #region 读取excel方法
        /// <summary>
        /// 获取sheet表名
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string[] GetSheetName(string filePath)
        {
            int sheetNumber = 0;
            var file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            if (filePath.IndexOf(".xlsx") > 0)
            {
                //2007版本
                var xssfworkbook = new XSSFWorkbook(file);
                sheetNumber = xssfworkbook.NumberOfSheets;

                string[] sheetNames = new string[sheetNumber];

                for (int i = 0; i < sheetNumber; i++)
                {
                    sheetNames[i] = xssfworkbook.GetSheetName(i);
                }
                return sheetNames;
            }
            else if (filePath.IndexOf(".xls") > 0)
            {
                //2003版本
                var hssfworkbook = new HSSFWorkbook(file);
                sheetNumber = hssfworkbook.NumberOfSheets;

                string[] sheetNames = new string[sheetNumber];

                for (int i = 0; i < sheetNumber; i++)
                {
                    sheetNames[i] = hssfworkbook.GetSheetName(i);
                }
                return sheetNames;
            }
            return null;
        }
        /// <summary>
        /// 根据表名获取表
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath, string sheetName)
        {
            string outMsg = "";
            var dt = new DataTable();
            string fileType = Path.GetExtension(filePath).ToLower();

            try
            {
                ISheet sheet = null;
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (fileType == ".xlsx")
                {
                    //2007版
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        dt = GetSheetDataTable(sheet, out outMsg);
                    }
                }
                else if (fileType == ".xls")
                {
                    //2003版
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        dt = GetSheetDataTable(sheet, out outMsg);
                    }
                }


            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


            return dt;
        }
      
        /// <summary>
        /// 根据表名获取表
        /// </summary>
        /// <param name="fs">数据流</param>
        /// <param name="fileName">文件流</param>
        /// <param name="sheetName">数据表名称</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTableFormStream(Stream fs,string fileName,string sheetName)
        {
            string outMsg = "";
            var dt = new DataTable();
            string fileType = Path.GetExtension(fileName).ToLower();

            try
            {
                ISheet sheet = null;
                //FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (fileType == ".xlsx")
                {
                    //2007版
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        dt = GetSheetDataTable(sheet, out outMsg);
                    }
                }
                else if (fileType == ".xls")
                {
                    //2003版
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        dt = GetSheetDataTable(sheet, out outMsg);
                    }
                }


            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


            return dt;
        }
        /// <summary>
        /// 获取sheet表对应的DataTable
        /// </summary>
        /// <param name="sheet">Excel工作表</param>
        /// <param name="strMsg"></param>
        /// <returns></returns>
        private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg)
        {
            strMsg = "";
            DataTable dt = new DataTable();
            string sheetName = sheet.SheetName;
            int startIndex = 0;// sheet.FirstRowNum;
            int lastIndex = sheet.LastRowNum;
            //合并单元格方法
            //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0));
            //最大列数
            int cellCount = 0;
            IRow maxRow = sheet.GetRow(0);
            for (int i = startIndex; i <= lastIndex; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null && cellCount < row.LastCellNum)
                {
                    cellCount = row.LastCellNum;
                    maxRow = row;
                }
            }
            //列名设置
            try
            {
                //maxRow.LastCellNum = 12 // L
                for (int i = 0; i < cellCount; i++)//maxRow.FirstCellNum
                {
                    //dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
                    dt.Columns.Add(maxRow.GetCell(i).StringCellValue);
                    //DataColumn column = new DataColumn("Column" + (i + 1).ToString());
                    //dt.Columns.Add(column);
                }
            }
            catch
            {
                strMsg = "工作表" + sheetName + "中无数据";
                return null;
            }
            //数据填充
            for (int i = startIndex; i <= lastIndex; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow drNew = dt.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                    {
                        if (row.GetCell(j) != null)
                        {
                            ICell cell = row.GetCell(j);
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    drNew[j] = "";
                                    break;
                                case CellType.Numeric:
                                    short format = cell.CellStyle.DataFormat;
                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                    if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176)
                                        drNew[j] = cell.DateCellValue;
                                    else
                                        drNew[j] = cell.NumericCellValue;
                                    if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
                                        drNew[j] = cell.NumericCellValue.ToString("#0.00");
                                    break;
                                case CellType.String:
                                    drNew[j] = cell.StringCellValue;
                                    break;
                                case CellType.Formula:
                                    try
                                    {
                                        drNew[j] = cell.NumericCellValue;
                                        if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
                                            drNew[j] = cell.NumericCellValue.ToString("#0.00");
                                    }
                                    catch
                                    {
                                        try
                                        {
                                            drNew[j] = cell.StringCellValue;
                                        }
                                        catch { }
                                    }
                                    break;
                                default:
                                    drNew[j] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }
                    dt.Rows.Add(drNew);
                }
            }
            return dt;
        }
        #endregion
    }
}
